/** 
 * @Title: Data2File.java 
 * @Package participle.file 
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月26日 下午6:49:03 
 */
package wordrelationship.dao;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import com.zizo.util.ConnectionPool;

/**
 * @ClassName: Data2File
 * @Description: TODO
 * @author HeYan
 * @date 2016年9月26日 下午6:49:03
 * 
 */
public class Data2FileRel implements Runnable {
	private Connection conn;
	private BufferedWriter br;
	private String table;

	/**
	 * @param conn
	 * @param br
	 * @param table
	 */
	public Data2FileRel(Connection conn, String table) {
		super();
		this.conn = conn;
		try {
			File file = new File("/var/server/java/heyan/file_wordrel/" + table);
			if (!file.exists()) {
				file.createNewFile();
			}
			this.br = new BufferedWriter(new FileWriter(file, true));
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.table = table;
	}

	public Data2FileRel(Connection conn) {
		super();
		this.conn = conn;
	}

	public List<String> getTables() {
		List<String> list = new ArrayList<>();
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT `table` as a FROM `table_word`";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				list.add(rs.getString(1));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public void getDataFromBBS() throws IOException {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT bbs_title,bbs_content FROM " + table;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				String s1 = rs.getString(1);
				String s2 = rs.getString(2);
				if (s1 != null)
					insertData2File(s1);
				if (s2 != null)
					insertData2File(s2);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void getDataFromKB() throws IOException {
		Statement st = null;
		ResultSet rs = null;
		String sql = "SELECT b.brand_autohomeClubId,a.kb_content FROM autohome_kb as a,carvoice_core.core_brand as b where a.kb_cid = b.brand_autohomeId and  kb_cid in (3405,3408,3411,3412,3413,3414,3415,3417,3422,3426,3429,3430,3442,3453,3454,3457,3460,3461,3462,3463,3465,3467,3472,3475,3481,3493,3518,3521,3524,3528,3530,3533,3535,3537,3545,3547,3553,3554,3556,3557,3574,3576,3581,3582,3586,3589,3591,3615,3618,3632,3634,3637,3657,3661,3662,3664,3672,3677,3683,3688,3691,3693,3695,3706,3726,3751,3758,3763,3766,3774,3777,3780,3782,3783,3786,3788,3789,3793,3795,3800,3802,3817,3823,3828,3829,3839,3857,3858,3859,3861,3862,3870,3871,3872,3873,3874,3876,3893,3896,3899,3913,3920,3922,3923,3924,3928,3934,3939,3954,3957,3959,3963,3964,3968,3970,3978,3980,3983,3987,3989,3994,3999,4009,4023,4031,4072,4077,4080,4083,4096,4105,4133,4139,790,3248,2294,2295,2297,2299,2310,2312,2313,2319,2323,2325,2334,2336,2337,2341,2348,2357,2381,2388,2402,2417,2418,2429,2472,2473,2478,2537,2540,2541,2543,2556,2560,2561,2562,2563,2565,2566,2567,2583,2615,2619,2664,2678,2716,2743,2751,2752,2761,2764,2767,2768,2769,2770,2771,2778,2779,2781,2785,2787,2788,2791,2805,2806,2838,2847,2852,2857,2863,2867,2871,2883,2886,2896,2922,2927,2944,2945,2949,2951,2952,2954,2955,2956,2957,2962,2963,2964,2966,2968,2974,2980,2987,2988,2989,2990,2991,2998,2999,3000,3006,3008,3013,3035,3040,3043,3053,3059,3065,3066,3068,3069,3073,3074,3075,3080,3081,3082,3084,3085,3086,3097,3099,3100,3103,3104,3112,3126,3128,3134,3154,3157,3158,3170,3171,3185,3191,3195,3197,3201,3204,3207,3209,3214,3216,3217,3220,3226,3234,3237,3267,3284,3286,3290,3292,3293,3294,3298,3300,3301,3306,3309,3312,3324,3326,3335,3341,3343,3347,3349,3351,3361,3384,3385,3397,16,18,22,45,46,49,50,51,52,53,59,60,64,65,66,67,68,75,78,81,82,86,98,99,101,102,107,109,110,111,117,121,135,145,146,153,155,159,163,164,166,172,177,178,196,197,201,209,210,224,227,261,270,271,281,285,286,287,290,311,314,333,341,342,345,352,358,362,363,364,365,371,373,375,382,395,398,403,404,407,409,412,413,425,432,438,442,443,447,448,453,454,458,462,466,469,470,473,474,482,483,496,504,508,519,521,522,523,526,528,529,532,537,538,564,565,578,579,580,586,587,588,589,592,596,597,601,602,606,608,614,616,617,620,622,623,632,633,634,639,650,655,656,657,659,660,669,686,688,692,693,700,740,749,750,758,770,771,777,780,792,793,798,799,801,802,812,813,823,825,831,833,834,835,837,841,844,850,859,871,872,873,874,875,876,877,878,880,882,898,913,927,940,951,961,964,965,982,983,987,989,1004,1005,1006,1007,2024,2027,2034,2041,2045,2051,2062,2063,2078,2084,2086,2088,2090,2107,2113,2115,2118,2119,2120,2123,2124,2126,2131,2134,2137,2139,2141,2144,2147,2155,2168,2190,2228,2236,2237,2242,2246,2248,2256,2268,4158,3307)";
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while (rs.next()) {
				int bbscid = rs.getInt(1);
				String s1 = rs.getString(2);
				if (s1 != null)
					insertkb(s1, bbscid);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void insertkb(String content, int bsbcid) throws IOException {
		BufferedWriter br = new BufferedWriter(
				new FileWriter(new File("/var/server/java/heyan/file_wordrel/bbs_c_" + bsbcid), true));
		br.write(content);
		br.close();
	}

	public final String getFilterData(String content) {
		String s = "[；。\\+\\——]|�|÷|→|※|∧|∥|∏|∑|√|∩|∪|∫|∮|☆|★|⊥|∷|∵|∨|⊙|□|▪|◎|〓|↑|〖|〗|＆|Ā|Ō|°|♀|☊ |々|「|」|￼|￣|\\*|・|ˇ|°|￥|￡|≈|≌|√|∠|∈|\\||∞|Š|Ū|Ǔ|Ò|Ê|=|\\d{6,}|、|\\?{3,}|▲|～|…{2,}|·|●|[A-Za-z]{6,}|―{3,}|◆|■|℃|%|％|\\.|￣|∀|｀|＾|］|⌒|＠{2,}|＄|＃|§| {3,}|﹏|　{2,}";
		Pattern p_1 = Pattern.compile(s);
		Matcher m_1 = p_1.matcher(content);
		String res_1 = m_1.replaceAll("");
		return res_1;
	}

	public void insertData2File(String data) throws IOException {
		br.write(getFilterData(data.replaceAll(
				"(http:?//|ftp:?//|https:?//|www){0,1}[^\u4e00-\u9fa5\\s]*?\\.(com|net|cn|me|tw|fr)[^\u4e00-\u9fa5\\s]*",
				"")) + "\n");
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see java.lang.Runnable
	 */
	@Override
	public void run() {
		try {
			getDataFromBBS();
			System.out.println(table);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				br.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public static void main(String[] args) throws IOException {
		ExecutorService fix = Executors.newFixedThreadPool(50);
		Connection conn = ConnectionPool.getConnection("autohome_syn", "192.169.0.168", "3306");
		Data2FileRel data = new Data2FileRel(conn, null);
		List<String> tables = data.getTables();
		for (String table : tables) {
			fix.execute(new Data2FileRel(conn, table));
		}
		fix.shutdown();
		while (true) {
			if (fix.isTerminated()) {
				new Data2FileRel(conn).getDataFromKB();
				System.out.println("KBGG");
				break;
			}
			try {
				Thread.sleep(100000);
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
		}
	}
}
